﻿Imports System.Collections.Generic
Imports System.Data.SqlClient
Imports Bussines.entity
Imports System.Data.SqlTypes
Public Class actasDAL
    Private conexion As New conexion

    Public Sub ListarActa()

    End Sub

    Public Function InsertarActa(ByVal BE As actasBE) As String
        Dim cmd As SqlCommand = New SqlCommand()
        Dim cmd2 As SqlCommand
        Dim cmd3 As SqlCommand
        Dim cmd4 As SqlCommand
        Dim cmd5 As SqlCommand
        Dim cmd6 As SqlCommand
        Dim cmd7 As SqlCommand
        Dim cmd8 As SqlCommand

        Dim cnn As SqlConnection
        Dim intIdActa As Int32
        Dim actasAsistencia As actasBE.AsistentesActa
        Dim actasAcuerdo As actasBE.AcuerdosActa
        Dim actasAgenda As actasBE.AgendaActas
        Dim actasDesaAgenda As actasBE.DesarrolloAgendaActas
        Dim actasAgendaPrelim As actasBE.AgendaPeliminarActas
        Dim actasDeConocimiento As actasBE.DeConocimiento
        'comenzamos a hacer la conexion a la bd
        cnn = conexion.abrir()
        Dim transac As SqlTransaction
        transac = cnn.BeginTransaction()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_insertarActas"

        Dim _idActa As New SqlParameter("@idActa", SqlDbType.Int)
        _idActa.Direction = ParameterDirection.Input
        _idActa.Value = BE.idActa
        cmd.Parameters.Add(_idActa)

        Dim _acta_num As New SqlParameter("@acta_num", SqlDbType.VarChar)
        _acta_num.Direction = ParameterDirection.Input
        _acta_num.Value = BE.acta_num
        cmd.Parameters.Add(_acta_num)


        Dim _idCompania As New SqlParameter("@idCompania", SqlDbType.Int)
        _idCompania.Direction = ParameterDirection.Input
        _idCompania.Value = BE.idCompania
        cmd.Parameters.Add(_idCompania)

        Dim _Equipo_id As New SqlParameter("@equipo_id", SqlDbType.VarChar)
        _Equipo_id.Direction = ParameterDirection.Input
        _Equipo_id.Value = BE.equipo_id
        cmd.Parameters.Add(_Equipo_id)

        Dim _Area As New SqlParameter("@Area", SqlDbType.VarChar)
        _Area.Direction = ParameterDirection.Input
        _Area.Value = BE.Area
        cmd.Parameters.Add(_Area)

        Dim _lugar As New SqlParameter("@lugar", SqlDbType.VarChar)
        _lugar.Direction = ParameterDirection.Input
        _lugar.Value = BE.lugar
        cmd.Parameters.Add(_lugar)


        Dim _moderador As New SqlParameter("@moderador", SqlDbType.VarChar)
        _moderador.Direction = ParameterDirection.Input
        _moderador.Value = BE.moderador
        cmd.Parameters.Add(_moderador)

        Dim _fechaActa As New SqlParameter("@fechaActa", SqlDbType.VarChar)
        _fechaActa.Direction = ParameterDirection.Input
        _fechaActa.Value = BE.fechaActa
        cmd.Parameters.Add(_fechaActa)

        Dim _fecanno As New SqlParameter("@fecanno", SqlDbType.VarChar)
        _fecanno.Direction = ParameterDirection.Input
        _fecanno.Value = BE.fecanno
        cmd.Parameters.Add(_fecanno)

        Dim _semana As New SqlParameter("@semana", SqlDbType.VarChar)
        _semana.Direction = ParameterDirection.Input
        _semana.Value = BE.semana
        cmd.Parameters.Add(_semana)

        Dim _inicio As New SqlParameter("@inicio", SqlDbType.VarChar)
        _inicio.Direction = ParameterDirection.Input
        _inicio.Value = BE.inicio
        cmd.Parameters.Add(_inicio)

        Dim _fin As New SqlParameter("@fin", SqlDbType.VarChar)
        _fin.Direction = ParameterDirection.Input
        _fin.Value = BE.fin
        cmd.Parameters.Add(_fin)

        Dim _duracion As New SqlParameter("@duracion", SqlDbType.VarChar)
        _duracion.Direction = ParameterDirection.Input
        _duracion.Value = BE.duracion
        cmd.Parameters.Add(_duracion)

        'Dim _secretario As New SqlParameter("@secretario", SqlDbType.VarChar)
        '_secretario.Direction = ParameterDirection.Input
        '_secretario.Value = BE.secretario
        'cmd.Parameters.Add(_secretario)

        Dim _Facilitador As New SqlParameter("@Facilitador", SqlDbType.VarChar)
        _Facilitador.Direction = ParameterDirection.Input
        _Facilitador.Value = BE.facilitador
        cmd.Parameters.Add(_Facilitador)

        Dim _Estado As New SqlParameter("@Estado", SqlDbType.VarChar)
        _Estado.Direction = ParameterDirection.Input
        _Estado.Value = BE.Estado
        cmd.Parameters.Add(_Estado)


        'Dim _sig_reu_fecha_val As SqlString
        'If BE.sig_reu_fecha Is Nothing Then
        '    _sig_reu_fecha_val = SqlString.Null
        'Else
        '    _sig_reu_fecha_val = BE.sig_reu_fecha
        'End If

        'Dim _sig_reu_fecha As New SqlParameter("@sig_reu_fecha", SqlDbType.VarChar)
        '_sig_reu_fecha.Direction = ParameterDirection.Input
        '_sig_reu_fecha.Value = _sig_reu_fecha_val
        'cmd.Parameters.Add(_sig_reu_fecha)

        'Dim _sig_reu_lugar As New SqlParameter("@sig_reu_lugar", SqlDbType.VarChar)
        '_sig_reu_lugar.Direction = ParameterDirection.Input
        '_sig_reu_lugar.Value = BE.sig_reu_lugar
        'cmd.Parameters.Add(_sig_reu_lugar)

        'Dim _sig_reu_hora As New SqlParameter("@sig_reu_hora", SqlDbType.VarChar)
        '_sig_reu_hora.Direction = ParameterDirection.Input
        '_sig_reu_hora.Value = BE.sig_reu_hora
        'cmd.Parameters.Add(_sig_reu_hora)

        Dim _flujo_acta As New SqlParameter("@flujo_acta", SqlDbType.VarChar)
        _flujo_acta.Direction = ParameterDirection.Input
        _flujo_acta.Value = BE.flujo_Acta
        cmd.Parameters.Add(_flujo_acta)

        Dim _id As New SqlParameter("@idact", SqlDbType.Int)
        _id.Direction = ParameterDirection.Output
        cmd.Parameters.Add(_id)

        Dim _otroLugar As New SqlParameter("@otroLugar", SqlDbType.VarChar)
        _otroLugar.Direction = ParameterDirection.Input
        _otroLugar.Value = BE.otroLugar
        cmd.Parameters.Add(_otroLugar)

        Dim _id_Asunto As New SqlParameter("@id_Asunto", SqlDbType.Int)
        _id_Asunto.Direction = ParameterDirection.Input
        _id_Asunto.Value = BE.id_Asunto
        cmd.Parameters.Add(_id_Asunto)

        Dim intInvitacion As SqlTypes.SqlInt32
        If BE.idInvitacion = 0 Then
            intInvitacion = SqlInt32.Null
        Else
            intInvitacion = BE.idInvitacion
        End If

        Dim _idInvitacion As New SqlParameter("@idInvitacion", SqlDbType.Int)
        _idInvitacion.Direction = ParameterDirection.Input
        _idInvitacion.Value = intInvitacion
        cmd.Parameters.Add(_idInvitacion)

        Dim _usuario_creado As New SqlParameter("@usuario_creado", SqlDbType.VarChar)
        _usuario_creado.Direction = ParameterDirection.Input
        _usuario_creado.Value = BE.usuario_creado
        cmd.Parameters.Add(_usuario_creado)

        Dim _usuario_modificado As New SqlParameter("@usuario_modificado", SqlDbType.VarChar)
        _usuario_modificado.Direction = ParameterDirection.Input
        _usuario_modificado.Value = BE.usuario_modificado
        cmd.Parameters.Add(_usuario_modificado)

        'Dim sig_reu_Otrolugar As New SqlParameter("@sig_reu_Otrolugar", SqlDbType.VarChar)
        'sig_reu_Otrolugar.Direction = ParameterDirection.Input
        'sig_reu_Otrolugar.Value = BE.sig_reu_OtroLugar
        'cmd.Parameters.Add(sig_reu_Otrolugar)

        Try
            cmd.Connection = cnn
            cmd.Transaction = transac
            cmd.ExecuteNonQuery()

            'insertamos todos los parametro para detalle de asistentes
            'creamos el parametro de comndo para insertar a todos
            intIdActa = cmd.Parameters("@idact").Value

            '##
            'insertamos el detalle de asistentes

            For Each actasAsistencia In BE.ListaAsistente
                cmd2 = New SqlCommand
                cmd2.Connection = cnn
                cmd2.Transaction = transac
                cmd2.CommandType = CommandType.StoredProcedure
                cmd2.CommandText = "sp_insertar_AsistenteActas"
                cmd2.Parameters.Add(New SqlParameter("@idCorrelativo", SqlDbType.Int)).Value = actasAsistencia.idCorrelativo
                cmd2.Parameters.Add(New SqlParameter("@idAsistente", SqlDbType.VarChar)).Value = actasAsistencia.idAsistente
                cmd2.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd2.Parameters.Add(New SqlParameter("@nombre", SqlDbType.VarChar)).Value = actasAsistencia.nombre
                cmd2.Parameters.Add(New SqlParameter("@cargo", SqlDbType.VarChar)).Value = actasAsistencia.cargo
                cmd2.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = actasAsistencia.activo
                cmd2.Parameters.Add(New SqlParameter("@Asistio", SqlDbType.Bit)).Value = actasAsistencia.Asistio
                cmd2.ExecuteNonQuery()
                cmd2.Dispose()
            Next

            ''insertamos el detalle de la agenda
            For Each actasAgenda In BE.ListAgendas
                cmd4 = New SqlCommand
                cmd4.Connection = cnn
                cmd4.Transaction = transac
                cmd4.CommandType = CommandType.StoredProcedure
                cmd4.CommandText = "sp_insertarAgenda"
                cmd4.Parameters.Add(New SqlParameter("@idAgenda", SqlDbType.Int)).Value = actasAgenda.idAgenda
                cmd4.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd4.Parameters.Add(New SqlParameter("@actividad", SqlDbType.VarChar, -1)).Value = actasAgenda.actividad
                cmd4.Parameters.Add(New SqlParameter("@idResponsable", SqlDbType.VarChar)).Value = actasAgenda.idResponsable
                'esta variable es variante ahora solo para mostrar
                cmd4.Parameters.Add(New SqlParameter("@Responsable", SqlDbType.VarChar)).Value = actasAgenda.Responsable
                'cmd4.Parameters.Add(New SqlParameter("@Tiempo_Asignado", SqlDbType.VarChar)).Value = actasAgenda.Tiempo_Asignado
                cmd4.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = actasAgenda.activo
                cmd4.ExecuteNonQuery()
                cmd4.Dispose()
            Next


            ''insertamos el detalle de el desarrollo de la agenda
            For Each actasDesaAgenda In BE.ListaDesarrolloAgenda
                cmd5 = New SqlCommand
                cmd5.Connection = cnn
                cmd5.Transaction = transac
                cmd5.CommandType = CommandType.StoredProcedure
                cmd5.CommandText = "sp_insertarDesarrolloAgenda"
                cmd5.Parameters.Add(New SqlParameter("@idDesAgenda", SqlDbType.Int)).Value = actasDesaAgenda.idDesarrolloAgenda
                cmd5.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd5.Parameters.Add(New SqlParameter("@actividad", SqlDbType.VarChar, -1)).Value = actasDesaAgenda.actividad
                'cmd5.Parameters.Add(New SqlParameter("@hora_inicio", SqlDbType.VarChar)).Value = actasDesaAgenda.hora_inicio
                'cmd5.Parameters.Add(New SqlParameter("@hora_final", SqlDbType.VarChar)).Value = actasDesaAgenda.hora_Final
                'cmd5.Parameters.Add(New SqlParameter("@tiempo_total", SqlDbType.VarChar)).Value = actasDesaAgenda.Tiempo_total
                cmd5.Parameters.Add(New SqlParameter("@ACTIVO", SqlDbType.Int)).Value = actasDesaAgenda.activo
                cmd5.ExecuteNonQuery()
                cmd5.Dispose()
            Next


            'insertamos el detalle de listado de acuerdos
            For Each actasAcuerdo In BE.ListaAcuerdo
                cmd3 = New SqlCommand
                cmd3.Connection = cnn
                cmd3.Transaction = transac
                cmd3.CommandType = CommandType.StoredProcedure
                cmd3.CommandText = "sp_insertarAcuerdos"
                Dim Plazo As SqlTypes.SqlString
                If actasAcuerdo.Plazo = "" Or actasAcuerdo.Plazo Is Nothing Then
                    Plazo = SqlTypes.SqlString.Null
                Else
                    Plazo = actasAcuerdo.Plazo
                End If
                cmd3.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd3.Parameters.Add(New SqlParameter("@idAcuerdo", SqlDbType.Int)).Value = actasAcuerdo.idAcuerdo
                cmd3.Parameters.Add(New SqlParameter("@detalle_acuerdo", SqlDbType.VarChar, -1)).Value = actasAcuerdo.detalle_acuerdo
                cmd3.Parameters.Add(New SqlParameter("@Plazo", SqlDbType.VarChar)).Value = Plazo
                cmd3.Parameters.Add(New SqlParameter("@idResponsable", SqlDbType.VarChar)).Value = actasAcuerdo.idResponsable
                cmd3.Parameters.Add(New SqlParameter("@idSolicitante", SqlDbType.VarChar)).Value = actasAcuerdo.idSolicitante
                cmd3.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = actasAcuerdo.activo
                cmd3.Parameters.Add(New SqlParameter("@estado", SqlDbType.VarChar)).Value = actasAcuerdo.estado
                cmd3.Parameters.Add(New SqlParameter("@Usuario_creado", SqlDbType.VarChar)).Value = actasAcuerdo.usuario_creado
                cmd3.Parameters.Add(New SqlParameter("@Usuario_modificado", SqlDbType.VarChar)).Value = actasAcuerdo.usuario_modificado
                cmd3.ExecuteNonQuery()
                cmd3.Dispose()
            Next

            'insertamos el detalle de  conocimiento
            For Each actasDeConocimiento In BE.listaDeConocimiento
                cmd7 = New SqlCommand
                cmd7.Connection = cnn
                cmd7.Transaction = transac
                cmd7.CommandType = CommandType.StoredProcedure
                cmd7.CommandText = "sp_insertarDeConocimiento"
                cmd7.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd7.Parameters.Add(New SqlParameter("@idDeConocimiento", SqlDbType.Int)).Value = actasDeConocimiento.idDeConocimiento
                cmd7.Parameters.Add(New SqlParameter("@idusuario", SqlDbType.VarChar)).Value = actasDeConocimiento.idUsuario
                cmd7.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = actasDeConocimiento.activo
                cmd7.Parameters.Add(New SqlParameter("@Usuario", SqlDbType.VarChar)).Value = actasDeConocimiento.Usuario
                'cmd7.Parameters.Add(New SqlParameter("@Cargo", SqlDbType.VarChar)).Value = actasDeConocimiento.cargo
                cmd7.Parameters.Add(New SqlParameter("@Asunto_Pendiente", SqlDbType.VarChar)).Value = actasDeConocimiento.Asunto_Pendiente
                cmd7.ExecuteNonQuery()
                cmd7.Dispose()
            Next

            'insertamos el problema
            For Each actasProblema In BE.listaProblema
                cmd8 = New SqlCommand
                cmd8.Connection = cnn
                cmd8.Transaction = transac
                cmd8.CommandType = CommandType.StoredProcedure
                cmd8.CommandText = "sp_insertarProblema"

                Dim FechaFin As SqlTypes.SqlString
                If actasProblema.Fecha_Fin = "" Or actasProblema.Fecha_Fin Is Nothing Then
                    FechaFin = SqlTypes.SqlString.Null
                Else
                    FechaFin = actasProblema.Fecha_Fin
                End If

                cmd8.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
                cmd8.Parameters.Add(New SqlParameter("@idProblema", SqlDbType.Int)).Value = actasProblema.idProblema
                cmd8.Parameters.Add(New SqlParameter("@Problema", SqlDbType.VarChar)).Value = actasProblema.Problema
                cmd8.Parameters.Add(New SqlParameter("@AccionCorrectiva", SqlDbType.VarChar)).Value = actasProblema.AccionCorrectiva
                cmd8.Parameters.Add(New SqlParameter("@idResponsable", SqlDbType.VarChar)).Value = actasProblema.idResponsable
                cmd8.Parameters.Add(New SqlParameter("@Responsable", SqlDbType.VarChar)).Value = actasProblema.Responsable
                cmd8.Parameters.Add(New SqlParameter("@Fecha_Fin", SqlDbType.VarChar)).Value = FechaFin
                cmd8.Parameters.Add(New SqlParameter("@activo", SqlDbType.Int)).Value = actasProblema.activo



                cmd8.ExecuteNonQuery()
                cmd8.Dispose()
            Next



            '@idActa int,      
            '@idDeConocimiento int,      
            '@idusuario VARCHAR(8),        
            '@activo int  

            'insertamos el detalle de  la agenda preliminar
            'For Each actasAgendaPrelim In BE.listaAgendaPrelim
            '    cmd6 = New SqlCommand
            '    cmd6.Connection = cnn
            '    cmd6.Transaction = transac
            '    cmd6.CommandType = CommandType.StoredProcedure
            '    cmd6.CommandText = "sp_insertar_agendaPreliminar"
            '    cmd6.Parameters.Add(New SqlParameter("@idAgendaPrelim", SqlDbType.Int)).Value = actasAgendaPrelim.idAgendaPrelim
            '    cmd6.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = intIdActa
            '    cmd6.Parameters.Add(New SqlParameter("@Agenda_Prelim", SqlDbType.VarChar, -1)).Value = actasAgendaPrelim.AgendaPrelim
            '    cmd6.Parameters.Add(New SqlParameter("@ACTIVO", SqlDbType.Int)).Value = actasAgendaPrelim.activo
            '    cmd6.ExecuteNonQuery()
            '    cmd6.Dispose()
            'Next



            transac.Commit()
            cmd.Dispose()
            cnn.Close()
            Return intIdActa.ToString()
        Catch ex As Exception
            transac.Rollback()
            Throw ex
        End Try

        'Dim _idActa As Integer
        'Dim _acta_num As String
        'Dim _Area As String
        'Dim _lugar As String
        'Dim _moderador As String
        'Dim _fecanno As String
        'Dim _semana As String
        'Dim _inicio As String
        'Dim _fin As String
        'Dim _duracion As String
        'Dim _secretario As String
        'Dim _Facilitador As String

        'cmd.Parameters.Add(new SqlParameter("",SqlDbType.Float)).Value =
        'Return True
    End Function

    Public Function listarActaSharepoint(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "Listar_actaSharepoint"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listarAccesoAsistenteXActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listarAccesoAsistenteXActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listarAccesoDeConocimientoXActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listarAccesoDeConocimientoXActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function


    Public Function listarAcuerdoSharepoint(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_Listar_AcuerdosSharepoint"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function


    Public Function listarAcuerdoSharepointModificarActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_Listar_AcuerdosSharepoint_ModificacionActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_AgendaxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AgendaxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_DeConocimientoxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_DeConocimientoxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_Problema(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_Problema"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_AsistentesxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AsistentesxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_AsistentesxActa_VerActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AsistentesxActa_VerActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function



    Public Function listar_DesarrolloAgendaxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_DesarrolloAgendaxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_AcuerdosxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AcuerdosxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_AcuerdosxActa_ActaModificacion(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AcuerdosxActa_ActaModificacion"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function ListarProblemasPorActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "usp_Problema_ListarProblemaPorActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function
    Public Function listar_AcgendaPrelimxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_AgendaPrelimxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function


    Public Function listar_ActaxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_ActaxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        End Try
    End Function

    Public Function listar_ActaxActaImpresion(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listar_ActaxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_AsistentesxActasCorreos(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listarAsistente_Enviarcorreo"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_DeConocimientoxActasCorreos(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listarDeConocimiento_Enviarcorreo"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_ModeradorSecretarioxActasCorreos(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listarCabeceraActa_EnviarCorreo"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function




    Public Function enviarMail_Publicaion_Acta(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_enviarMail_Publicaion_Acta"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function


    Public Function listar_AcuerdosEnviarCorreos(ByVal idActa As Integer, ByVal Trab_id As String) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_ListarAcuerdos_EnviarCorreo"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        cmd.Parameters.Add(New SqlParameter("@Trab_id", SqlDbType.VarChar)).Value = Trab_id
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_AcuerdosConcluidosCorreos(ByVal idAcuerdoSP As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_Listar_AcuerdoConcludoCorreo"
        cmd.Parameters.Add(New SqlParameter("@idAcuerdoSP", SqlDbType.Int)).Value = idAcuerdoSP
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_AgrupacionResponsablesxActa(ByVal idActa As Integer) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_AgruparResponsablesxActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function Existe_Acta(ByVal idActa As Integer) As Boolean
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_existe_Acta"
        cmd.Parameters.Add(New SqlParameter("@IDACTA", SqlDbType.Int)).Value = idActa
        cmd.Parameters.Add(New SqlParameter("@ESTADO", SqlDbType.Bit)).Direction = ParameterDirection.Output

        Try
            cmd.Connection = conexion.abrir()
            cmd.ExecuteNonQuery()
            Dim blnEstado As Boolean = Convert.ToBoolean(cmd.Parameters("@ESTADO").Value)
            conexion.Cerrar()
            Return blnEstado
        Catch ex As Exception
            Return Nothing
        Finally

        End Try
    End Function

    Public Function Actualizar_idShrepointAcuerdo(ByVal idActa As Integer, ByVal idAcuerdo As Integer, ByVal idSPAcuerdo As Integer) As String
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = conexion.abrir()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_actualizaridSPAcuerdo"
        cmd.Parameters.Add(New SqlParameter("@idAcuerdo", SqlDbType.Int)).Value = idAcuerdo
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        cmd.Parameters.Add(New SqlParameter("@idSPAcuerdo", SqlDbType.Int)).Value = idSPAcuerdo
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            conexion.Cerrar()
            Return ""
        Catch ex As Exception
            Return ex.Message
        Finally

        End Try
    End Function

    Public Function Actualizar_idSharepointActa(ByVal idActa As Integer, ByVal idSPActa As Integer) As String
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.Connection = conexion.abrir()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "[sp_actualizaridSPActa]"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        cmd.Parameters.Add(New SqlParameter("@idActaSharepoint", SqlDbType.Int)).Value = idSPActa
        Try
            cmd.ExecuteNonQuery()
            cmd.Dispose()
            conexion.Cerrar()
            Return ""
        Catch ex As Exception
            Return ex.Message
        Finally

        End Try
    End Function

    Public Sub Actualizar_EstadoAcuerdo(ByVal idAcuerdo As Integer, ByVal idActa As Integer, ByVal Estado As String)
        Dim cmd As SqlCommand
        cmd = New SqlCommand
        cmd.Connection = conexion.abrir()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_ActualizarEstadoAcuerdo"
        cmd.Parameters.Add(New SqlParameter("@idAcuerdo", SqlDbType.Int)).Value = idAcuerdo
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        cmd.Parameters.Add(New SqlParameter("@Estado", SqlDbType.VarChar)).Value = Estado
        cmd.ExecuteNonQuery()
        cmd.Dispose()
    End Sub

    Public Function listar_AcuerdosPoderosa(ByVal Usuario As String) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.Text
        cmd.CommandText = "execute poderosa.dbo.SL_Usuarios_Alertas_actas_SP '01','" & Usuario & "'"
        Try
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function listar_AcuerdosActas(ByVal Usuario As String) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "sp_listarAcuerdosxUsuario"
        Try
            cmd.Parameters.Add(New SqlParameter("@dominio", SqlDbType.VarChar)).Value = Usuario
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function GrupoModeradoresxActa(ByVal idActa As Int32) As String
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_GrupoModeradoresxActa"
        Try
            cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
            cmd.Connection = conexion.abrir()
            Dim strGrupoMod As String = cmd.ExecuteScalar().ToString()
            conexion.Cerrar()
            Return strGrupoMod
        Catch ex As Exception
            Return ""
        Finally
        End Try
    End Function

    Public Sub EliminarFilas_DetalleActa(ByVal idActa As Int32)

        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_EliminarFilas_DetalleActa"
        cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
        cmd.Connection = conexion.abrir()
        Try
            cmd.ExecuteNonQuery()
            conexion.Cerrar()
        Catch ex As Exception
            Throw ex
        Finally
        End Try
    End Sub



    Public Function ListarUsuariodirectory_PermisosXActa(ByVal idActa As Int32) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_sp_listar_Usuariodirectory_PermisosXActa"
        Try
            cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function ListarAsistentesPermisos(ByVal idActa As Int32) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "ListarAsistentesPermisos"
        Try
            cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function enviarMail_Conclusion_Acuerdo(ByVal idActa As Int32) As DataTable
        Dim dt As New DataTable
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pdo_enviarMail_Conclusion_Acuerdo"
        Try
            cmd.Parameters.Add(New SqlParameter("@idActa", SqlDbType.Int)).Value = idActa
            cmd.Connection = conexion.abrir()
            dt.Load(cmd.ExecuteReader())
            conexion.Cerrar()
            Return dt
        Catch ex As Exception
            Return dt
        Finally

        End Try
    End Function

    Public Function RecuperarDenominacionActual() As String
        Dim denominacion As String = ""
        Dim cmd As SqlCommand = New SqlCommand()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "usp_RecuperarDenominacionAnual"
        Try

            cmd.Connection = conexion.abrir()
            denominacion = cmd.ExecuteScalar.ToString()
            conexion.Cerrar()
            Return denominacion
        Catch ex As Exception
            Return denominacion
        Finally

        End Try
    End Function
End Class
